#基金持仓占比获取
# http://fundf10.eastmoney.com/zcpz_519062.html
import requests
import time
from lxml import etree
import DataBase


#反复调用
def get_url(url, params=None, proxies=None):
    fail_time = 0
    cotinue_flag = True
    while (fail_time<=3&cotinue_flag==True):
        try:
            rsp = requests.get(url, params=params, proxies=proxies)
            cotinue_flag = False
        except Exception as e:
            print(e)
            cotinue_flag=True
            fail_time+=1
            print("睡眠5秒")
            time.sleep(5)
    return rsp.text


def get_position(code):
    url = 'http://fundf10.eastmoney.com/zcpz_{}.html'.format(code)
    resultList = []
    text =get_url(url)
    html_obj = etree.HTML(text)
    trs = html_obj.xpath('//table[@class="w782 comm tzxq"]/tbody/tr')#获取表格内容
    ths = html_obj.xpath('//table[@class="w782 comm tzxq"]/thead/tr/th')#获取表头
    dict = {'报告期': 1, '股票占净比': 2, '债券占净比': 3, '现金占净比': 4, '存托凭证占净比': 5, '净资产（亿元）': 6}#数据库存储顺序
    #组成新的映射关系
    new_dict={}
    for i in range(len(ths)):
        new_dict[i] = dict[ths[i].text]
    if len(trs)>0:
        for tr in trs:
            result=['---']*7
            tds = tr.xpath("td")
            result[0]=code
            for i in range(len(tds)):
                result[new_dict[i]]=(tds[i].text)
            resultList.append(result)
    return resultList


if __name__=="__main__":

    dbutil = DataBase.DataBase('test')
    select_sql = 'select code from foundation'


    insert_sql = "insert into foundation_position (code,report_date,proportion_of_shares,proportion_of_bond,proportion_of_receipt,proportion_of_cash,net_assets) " \
                 "values (%s, %s, %s,%s,%s,%s,%s) " \
                 "ON DUPLICATE KEY UPDATE proportion_of_shares = VALUES(proportion_of_shares),proportion_of_bond=VALUES(proportion_of_bond), " \
                 "proportion_of_receipt=VALUES(proportion_of_receipt),proportion_of_cash=VALUES(proportion_of_cash),net_assets=VALUES(net_assets) "
    codes = dbutil.select_list(select_sql)
    for i in range(len(codes)):
        resultList = get_position(codes[i][0])
        dbutil.batch_insert(insert_sql, tuple(resultList))

    sql = "select *from foundation_position where report_date='2017-09-30'" \
          "and proportion_of_bond!='---'" \
          "and proportion_of_bond >= '80.00%' " \
          "and proportion_of_shares = '---'"


